-- SQL Manager 2005 for SQL Server (2.4.0.1)
-- ---------------------------------------
-- Host      : PC2\SQLEXPRESS
-- Database  : ClickSocial
-- Version:  : Microsoft SQL Server  9.00.1399.06


--
-- Dropping foreign key FK_ItemVenta_ItemVenta : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[FK_ItemVenta_ItemVenta]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
  ALTER TABLE [dbo].[cs_Venta_Item]
  DROP CONSTRAINT [FK_ItemVenta_ItemVenta]
GO

--
-- Dropping stored procedure cs_venta_Item_spGetAllByEstadoCliente : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_Item_spGetAllByEstadoCliente]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_Item_spGetAllByEstadoCliente]
GO

--
-- Dropping stored procedure cs_venta_Item_spGetAllByVentaID : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_Item_spGetAllByVentaID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_Item_spGetAllByVentaID]
GO

--
-- Dropping stored procedure cs_venta_Item_spGetAllByVentaIDFotografo : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_Item_spGetAllByVentaIDFotografo]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_Item_spGetAllByVentaIDFotografo]
GO

--
-- Dropping stored procedure cs_venta_item_spInsert : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_item_spInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_item_spInsert]
GO

--
-- Dropping stored procedure cs_venta_spBuscarVentas : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spBuscarVentas]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spBuscarVentas]
GO

--
-- Dropping stored procedure cs_venta_spDelete : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spDelete]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spDelete]
GO

--
-- Dropping stored procedure cs_venta_spGetAll : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spGetAll]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spGetAll]
GO

--
-- Dropping stored procedure cs_venta_spGetAllByCliente : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spGetAllByCliente]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spGetAllByCliente]
GO

--
-- Dropping stored procedure cs_venta_spGetAllByFotografo : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spGetAllByFotografo]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spGetAllByFotografo]
GO

--
-- Dropping stored procedure cs_venta_spGetOne : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spGetOne]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spGetOne]
GO

--
-- Dropping stored procedure cs_venta_spGetOnebyCliente : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spGetOnebyCliente]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spGetOnebyCliente]
GO

--
-- Dropping stored procedure cs_venta_spInsert : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spInsert]
GO

--
-- Dropping stored procedure cs_venta_spUpdate : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spUpdate]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spUpdate]
GO

--
-- Dropping stored procedure cs_venta_spUpdateEstado : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_venta_spUpdateEstado]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[cs_venta_spUpdateEstado]
GO

--
-- Dropping table cs_Venta : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_Venta]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[cs_Venta]
GO

--
-- Dropping table cs_Venta_Item : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[cs_Venta_Item]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE [dbo].[cs_Venta_Item]
GO

--
-- Structure for table cs_Venta : 
--

CREATE TABLE [dbo].[cs_Venta] (
  [VentaID] bigint IDENTITY(1, 1) NOT NULL,
  [Cliente] nvarchar(256) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [Monto] money NULL,
  [Fecha] datetime NOT NULL,
  [Estado] varchar(50) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [Cantidad] int NULL,
  [FechaMod] datetime NULL
)
ON [PRIMARY]
GO

--
-- Structure for table cs_Venta_Item : 
--

CREATE TABLE [dbo].[cs_Venta_Item] (
  [VentaItemID] bigint IDENTITY(1, 1) NOT NULL,
  [Nombre] nvarchar(256) COLLATE Modern_Spanish_CI_AS NULL,
  [Descripcion] nvarchar(2000) COLLATE Modern_Spanish_CI_AS NULL,
  [Precio] money NULL,
  [VentaID] bigint NOT NULL,
  [EventoID] bigint NULL,
  [Fotografo] nvarchar(256) COLLATE Modern_Spanish_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for indices : 
--

ALTER TABLE [dbo].[cs_Venta]
ADD CONSTRAINT [PK_cs_Ventas] 
PRIMARY KEY CLUSTERED ([VentaID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[cs_Venta_Item]
ADD CONSTRAINT [PK_ItemVenta] 
PRIMARY KEY CLUSTERED ([VentaItemID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

--
-- Definition for foreign keys : 
--

ALTER TABLE [dbo].[cs_Venta_Item]
ADD CONSTRAINT [FK_ItemVenta_ItemVenta] FOREIGN KEY ([VentaID]) 
  REFERENCES [dbo].[cs_Venta] ([VentaID]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

--
-- Definition for stored procedure cs_venta_Item_spGetAllByEstadoCliente : 
--

CREATE PROCEDURE dbo.cs_venta_Item_spGetAllByEstadoCliente
@Cliente nvarchar(256),
@Estado varchar(50)
AS
BEGIN
     SELECT cs_Venta_Item.VentaItemID,
            cs_Venta_Item.Nombre,
            cs_Venta_Item.Descripcion,
            cs_Venta_Item.Precio,
            cs_Venta_Item.VentaID,
            cs_Venta_Item.EventoID,
            cs_Venta_Item.Fotografo
     FROM cs_Venta_Item
     inner join cs_Venta on cs_Venta.VentaID=cs_Venta_Item.VentaID
     where cs_Venta.Cliente=@Cliente and cs_Venta.Estado=@Estado
END
GO

--
-- Definition for stored procedure cs_venta_Item_spGetAllByVentaID : 
--

CREATE PROCEDURE dbo.cs_venta_Item_spGetAllByVentaID
@VentaID bigint
AS
BEGIN
SELECT VentaItemID,
       Nombre,
       Descripcion,
       Precio,
       VentaID,
       EventoID,
       Fotografo
FROM [dbo].[cs_Venta_Item] 
where VentaID = @VentaID
END
GO

--
-- Definition for stored procedure cs_venta_Item_spGetAllByVentaIDFotografo : 
--

CREATE PROCEDURE dbo.cs_venta_Item_spGetAllByVentaIDFotografo
@VentaID bigint,
@Fotografo nvarchar(256)
AS
BEGIN
SELECT VentaItemID,
       Nombre,
       Descripcion,
       Precio,
       VentaID,
       EventoID,
       Fotografo
FROM [dbo].[cs_Venta_Item]
where VentaID = @VentaID
and Fotografo = @Fotografo
END
GO

--
-- Definition for stored procedure cs_venta_item_spInsert : 
--

CREATE PROCEDURE dbo.cs_venta_item_spInsert
@Nombre nvarchar(256),
@Descripcion nvarchar(2000),
@Precio money,
@VentaID bigint,
@EventoID bigint,
@Fotografo nvarchar(256)
AS
BEGIN
insert into [dbo].[cs_Venta_Item](
       Nombre,
       Descripcion,
       Precio,
       VentaID,
       EventoID,
       Fotografo)
       values(@Nombre,
       @Descripcion,
       @Precio,
       @VentaID,
       @EventoID,
       @Fotografo)
END
GO

--
-- Definition for stored procedure cs_venta_spBuscarVentas : 
--

CREATE   PROCEDURE dbo.cs_venta_spBuscarVentas
@Desde datetime=null,
@Hasta datetime=null,
@Estado varchar(50),
@Campo nvarchar(256),
@Texto nvarchar(2000),
@Fotografo nvarchar(256)
AS
BEGIN

declare @select nvarchar(4000)
declare @from nvarchar(4000)
declare @where nvarchar(4000)
declare @params nvarchar(4000)
declare @query  nvarchar(4000) 
declare @orderby nvarchar(100)
declare @aux varchar(10)


set @select = N'select cs_Venta.VentaID,cs_Venta.Cliente,cs_Venta.Fecha,cs_Venta.Estado,cs_Venta.FechaMod'
set @from = N' from cs_Venta '
set @where = N''
set @orderby = N' order by cs_Venta.fecha desc'
set @params = N'@fecha_desde datetime ,@fecha_hasta datetime, @Estado1 varchar(50),@Texto1 nvarchar(2000),@Fotografo1 nvarchar(256)'
set @aux = 'where '
--set @Campo = N'cs_Venta.' + @Campo

if @Desde is not null
begin
   set @where = @aux + N' cs_Venta.Fecha >= @fecha_desde '
   set @aux = 'and '
end

if @Hasta is not null
begin
   set @where = @where + @aux + N' cs_Venta.Fecha < (@fecha_hasta+1) '
   set @aux = 'and '
end

if @Estado!=''
begin
   set @where = @where + @aux + N' cs_Venta.Estado = @estado1 '
   set @aux = 'and '
end

if @Campo!=''
begin
   set @where =  @where + @aux + @Campo + N' like ''%''+@Texto1+''%'' '
   set @aux = 'and '
end

if @Fotografo!=''
begin
   set @select = @select + N',sum(cs_Venta_Item.Precio) as Monto,count(*) as Cantidad,cs_Venta_Item.Fotografo '
   set @from = @from + N'inner join cs_Venta_Item on cs_Venta.VentaID=cs_Venta_Item.VentaID ' + @where
   set @from = @from + N'group by cs_Venta.VentaID,cs_Venta.Cliente,cs_Venta.Fecha,cs_Venta.Estado,cs_Venta.FechaMod,cs_Venta_Item.Fotografo '
   set @from = @from + N'HAVING cs_Venta_Item.Fotografo = @Fotografo1 '   
end
else   
begin
   set @select = @select + N',cs_Venta.Monto,cs_Venta.Cantidad '
   set @from = @from + @where
end

set @query = @select + @from + @orderby

--Print @select + @from + @where + @orderby
EXECUTE sp_executesql @query, @params, @fecha_desde=@Desde,@fecha_hasta=@Hasta,@Estado1=@Estado,@Texto1=@Texto,@Fotografo1=@Fotografo
END
GO

--
-- Definition for stored procedure cs_venta_spDelete : 
--

CREATE PROCEDURE dbo.cs_venta_spDelete
@VentaID bigint
AS
BEGIN
  delete from cs_Venta where VentaID = @VentaID
END
GO

--
-- Definition for stored procedure cs_venta_spGetAll : 
--

CREATE PROCEDURE dbo.cs_venta_spGetAll
AS
BEGIN
  SELECT cs_Venta.VentaID,
       cs_Venta.Cliente,
       cs_Venta.Monto,
       cs_Venta.Fecha,
       cs_Venta.Estado,
       cs_Venta.Cantidad,
       cs_Venta.FechaMod
--       cs_Venta_Item.Fotografo
  FROM cs_Venta
  inner join cs_Venta_Item
  on cs_Venta.VentaID = cs_Venta_Item.VentaID
  group by
       cs_Venta.VentaID,
       cs_Venta.Cliente,
       cs_Venta.Monto,
       cs_Venta.Fecha,
       cs_Venta.Estado,
       cs_Venta.Cantidad,
       cs_Venta.FechaMod
       --cs_Venta_Item.Fotografo
       --  HAVING cs_Venta_Item.Fotografo = 'admin1'
  order by cs_Venta.Fecha desc
END
GO

--
-- Definition for stored procedure cs_venta_spGetAllByCliente : 
--

CREATE PROCEDURE dbo.cs_venta_spGetAllByCliente
@Cliente nvarchar(256)
AS
BEGIN
    SELECT VentaID,
       Cliente,
       Monto,
       Fecha,
       Estado,
       Cantidad,
       FechaMod
  FROM [dbo].[cs_Venta]
  where Cliente = @Cliente
END
GO

--
-- Definition for stored procedure cs_venta_spGetAllByFotografo : 
--

CREATE PROCEDURE dbo.cs_venta_spGetAllByFotografo
@Fotografo nvarchar(256)
AS
BEGIN
  SELECT cs_Venta.VentaID,
       cs_Venta.Cliente,
       sum(cs_Venta_Item.Precio) as Monto,
       cs_Venta.Fecha,
       cs_Venta.Estado,
       count(*) as Cantidad,
       cs_Venta.FechaMod,
       cs_Venta_Item.Fotografo
  FROM cs_Venta
  inner join cs_Venta_Item
  on cs_Venta.VentaID = cs_Venta_Item.VentaID
  group by
       cs_Venta.VentaID,
       cs_Venta.Cliente,
       cs_Venta.Fecha,
       cs_Venta.Estado,
       cs_Venta.FechaMod,
       cs_Venta_Item.Fotografo
  HAVING cs_Venta_Item.Fotografo = @Fotografo
  order by cs_Venta.Fecha desc
  
  
END
GO

--
-- Definition for stored procedure cs_venta_spGetOne : 
--

CREATE PROCEDURE dbo.cs_venta_spGetOne
@VentaID bigint
AS
BEGIN
  SELECT VentaID,
       Cliente,
       Monto,
       Fecha,
       Estado,
       Cantidad,
       FechaMod
  FROM [dbo].[cs_Venta]
  where VentaID = @VentaID
END
GO

--
-- Definition for stored procedure cs_venta_spGetOnebyCliente : 
--

CREATE PROCEDURE dbo.cs_venta_spGetOnebyCliente
@VentaID bigint,
@Cliente nvarchar(256)
AS
BEGIN
  SELECT VentaID,
       Cliente,
       Monto,
       Fecha,
       Estado,
       Cantidad,
       FechaMod
  FROM [dbo].[cs_Venta]
  where VentaID = @VentaID and Cliente = @Cliente
END
GO

--
-- Definition for stored procedure cs_venta_spInsert : 
--

CREATE PROCEDURE dbo.cs_venta_spInsert
@Cliente nvarchar(256),
@Monto money,
@Fecha datetime,
@Estado nvarchar(50),
@Cantidad int
AS
BEGIN
     insert into [dbo].[cs_Venta]
     ( Cliente,
       Monto,
       Fecha,
       Estado,
       Cantidad)
       values(
       @Cliente,
       @Monto,
       @Fecha,
       @Estado,
       @Cantidad)
       
       IF @@ERROR <> 0
       begin
		SELECT @@ERROR
		return
	   end
       select @@Identity
END
GO

--
-- Definition for stored procedure cs_venta_spUpdate : 
--

CREATE PROCEDURE dbo.cs_venta_spUpdate
@VentaID bigint,
@Cliente nvarchar(256),
@Monto money,
@Fecha datetime,
@Estado nvarchar(50),
@Cantidad int
AS
BEGIN
  update [dbo].[cs_Venta]
  set  Cliente=@Cliente,
       Monto=@Monto,
       Fecha=@Fecha,
       Estado=@Estado,
       Cantidad=@Cantidad,
       FechaMod=getdate()
  where VentaID=@VentaID
END
GO

--
-- Definition for stored procedure cs_venta_spUpdateEstado : 
--

CREATE PROCEDURE dbo.cs_venta_spUpdateEstado
@VentaID bigint,
@Estado varchar(50)
AS
BEGIN
    update [dbo].[cs_Venta]
    set Estado=@Estado
    where VentaID=@VentaID
END
GO

CREATE PROCEDURE cs_venta_spDeleteByFotografo
@VentaID bigint,
@UserFotografo nvarchar(256)
AS
BEGIN

declare @cant int

  if exists (select * from cs_Venta_Item
  where cs_Venta_Item.VentaID = @VentaID
  and cs_Venta_Item.Fotografo = @UserFotografo)
  begin
    delete from cs_Venta_Item
    where cs_Venta_Item.VentaID = @VentaID
    and cs_Venta_Item.Fotografo = @UserFotografo
  end

  if not exists (select * from cs_Venta_Item
  where cs_Venta_Item.VentaID = @VentaID)
  begin
       delete from cs_Venta where VentaID = @VentaID
  end
  else
  begin
       select @cant=count(*) from cs_Venta_Item where cs_Venta_Item.VentaID = @VentaID
       update cs_Venta set Cantidad=@cant where VentaID = @VentaID
  end
  
END
GO